package com.shengbangtec.mapper;

import com.shengbangtec.model.TBackSalaryClueDO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shengbangtec.vo.CaseManagerVO;
import com.shengbangtec.vo.ClueInfoEveryDayVO;
import com.shengbangtec.vo.ClueInfoForCityVO;
import com.shengbangtec.vo.ClueInfoForMapVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author LBY
 * @since 2021-07-05
 */
@Mapper
public interface TBackSalaryClueMapper extends BaseMapper<TBackSalaryClueDO> {

    /**
     *  石家庄线索详情
     * @return
     */
    ClueInfoForCityVO clueInfoForCity();

    /**
     *  地图
     * @return
     */
    List<ClueInfoForMapVO> clueInfoForMap();

    /**
     *  每日线索走势
     * @return
     */
    List<ClueInfoEveryDayVO> clueInfoEveryDay(Map<String,Object> param);

    /**
     *  办结线索去年
     * @return
     */
    Map<String, BigDecimal> handleCountCompareLast(Map<String,Object> param);

    /**
     *  已解决人数去年
     * @return
     */
    Map<String,BigDecimal> handlePeopleCompareLast(Map<String,Object> param);

    /**
     *  已解决总金额去年
     * @return
     */
    Map<String,BigDecimal> handleAmountCompareLast(Map<String,Object> param);

    /**
     *  办结线索去年
     * @return
     */
    Map<String,BigDecimal> handleCountCompareThis(Map<String,Object> param);

    /**
     *  已解决人数去年
     * @return
     */
    Map<String,BigDecimal> handlePeopleCompareThis(Map<String,Object> param);

    /**
     *  已解决总金额去年
     * @return
     */
    Map<String,BigDecimal> handleAmountCompareThis(Map<String,Object> param);

    /**
     * 接受线索条数
     * @return
     */
    @Select("select ac.ahb01a,(case when substr(ac.ahb01b,8)='循环化工园区' then '化工园区' else substr(ac.ahb01b,8) end)ahb01b,\n" +
            "ac.tenDay,ac.twentyDay,ac.overTwentyDay,(ac.tenDay+ac.twentyDay+ac.overTwentyDay+ac.noReceive+ac.alreadyReceive) as sumDay,ac.noReceive,ac.alreadyReceive\n" +
            "from (select ahb01a,ahb01b,\n" +
            "sum(case when \n" +
            " ((sysdate - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400000)-acceptTime <1000 * 60 * 60 * 24 * 10 and ahb013 = '2'\n" +
            "then 1 else 0 end) tenDay,\n" +
            "sum(case when \n" +
            "((sysdate - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400000)-acceptTime between 1000 * 60 * 60 * 24 * 10  and 1000 * 60 * 60 * 24 * 20 and ahb013 = '2'\n" +
            "then 1 else 0 end) twentyDay,\n" +
            "sum(case when \n" +
            "((sysdate - to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss'))*86400000)-acceptTime >= 1000 * 60 * 60 * 24 * 20 and ahb013 = '2'\n" +
            "then 1 else 0 end) overTwentyDay,\n" +
            "sum(case when ahb013 = '1' then 1 else 0 end) noReceive,\n" +
            "sum(case when ahb013 in('3','4','5','6') then 1 else 0 end) alreadyReceive\n" +
            "FROM t_back_salary_clue\n" +
            "where AHB01B !='河北省石家庄市'\n" +
            "group by ahb01a,ahb01b) ac\n" +
            "order by sumDay desc\n")
    List<Map<String, Object>> getNumbersOfClues();

    /**
     * 涉及总金额
     * @return
     */
    @Select("select to_char(sum(ahb01q)/10000,'9999999990.99') sumAmout,ahb01a,(case when substr(ahb01b,8)='循环化工园区' then '化工园区' else substr(ahb01b,8) end)ahb01b\n" +
            "from t_back_salary_clue\n" +
            "WHERE AHB01B !='河北省石家庄市'\n" +
            "group by ahb01a,ahb01b\n" +
            "order by sumAmout desc")
    List<Map<String, Object>> getInvolvingSumAmount();

    /**
     * 建设领域饼状图
     * @return
     */
    @Select("select \n" +
            "to_char(sum(case when bhb012 ='1' then 1 else 0 end)/count(*),'9999999990.99')*100 government,\n" +
            "to_char(sum(case when bhb012 ='2' then 1 else 0 end)/count(*),'9999999990.99')*100 state,\n" +
            "to_char(sum(case when bhb012 ='9' or bhb012 is null then 1 else 0 end)/count(*),'9999999990.99')*100 other\n" +
            "from t_back_salary_clue where ahb015s=#{type}")
    Map<String, Object> getConstructionField(@Param("type") String type);

    /**
     * 待接收线索
     * @return
     */
    @Select("select rownum,a.ahb015,a.bhb016,a.ahb01b,a.ahb01p,a.ahb01q,a.adTime,a.ahb016\n" +
            "from (select (case when ahb015='1' then '工程' else '非工程' end)ahb015,nvl(bhb016,'暂无数据') bhb016,\n" +
            "(case when substr(nvl(ahb01b,'暂无数据'),8)='循环化工园区' then '化工园区' else substr(nvl(ahb01b,'暂无数据'),8) end)ahb01b,\n" +
            "nvl(ahb01p,0)ahb01p,nvl(ahb01q,0)ahb01q,nvl(to_char(adTime / (1000 * 60 * 60 * 24)\n" +
            "+ to_date('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD'),'暂无数据')adTime,ahb016\n" +
            "from t_back_salary_clue\n" +
            "where ahb013='1' and ahb01b !='河北省石家庄市' and adTime is not null\n" +
            "order by adTime desc) a")
    List<Map<String, Object>> getStayReceiveClues();

}
